﻿using EFCore.Models;
using HandyControl.Controls;
using Microsoft.EntityFrameworkCore;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections;
using System.Data;
using System.IO;
using System.Windows.Forms;
using MessageBox = HandyControl.Controls.MessageBox;

namespace NewWms
{
    public static class ExcelImport
    {

       
        //导入物料属性
        public static void ExcelImportMaterialProperty()
        {
            
            string excelName = OpenfileDialog();
                var dataTable = ExcelImportMethod(excelName);
                if (dataTable == null)
                {
                    return;
                }
                bool hasDuplicates = dataTable.AsEnumerable()
                                             .GroupBy(row => row.Field<string>("MaterialProertyname"))
                                             .Any(group => group.Count() > 1);
                if (hasDuplicates)
                {
                    MessageBox.Show("有属性名重复的数据,请检查");

                    return;
                }


                using (MyDbContext dbContext = new MyDbContext())
                {
                    var materials = dbContext.MaterialProperties;

                    foreach (DataRow row in dataTable.Rows)
                    {
                        bool res = materials.Any(x => x.MaterialProertyname == row["MaterialProertyname"].ToString());
                        if (res)
                        {
                            continue;
                        }
                        MaterialProperty materialProperty = new MaterialProperty();
                        materialProperty.MaterialProertyname = row["MaterialProertyname"].ToString();
                        materialProperty.Unit = row["Unit"].ToString();
                        materialProperty.Type = row["Type"].ToString();
                        materialProperty.Type = row["Type"].ToString();
                        materialProperty.Description = row["Description"].ToString();
                        materialProperty.MaterialProertyid = Guid.NewGuid();
                        materials.Add(materialProperty);
                    }
                    dbContext.SaveChangesAsync();
                    MessageBox.Show("导入成功");
                }
     
     
        }
        //导入库存物料
        public static void ExcelImportMaterialBase()
        {
            string excelName = OpenfileDialog();
            var dataTable = ExcelImportMethod(excelName);
            if (dataTable == null)
            {
                return;
            }
            bool hasDuplicates = dataTable.AsEnumerable()
                                         .GroupBy(row => row.Field<string>("MaterialCode"))
                                         .Any(group => group.Count() > 1);
            if (hasDuplicates)
            {
                MessageBox.Show("有属性名重复的数据,请检查");

                return;
            }
            using (MyDbContext dbContext = new MyDbContext())
            {
                var materials = dbContext.MaterialBases;
                var boxgrids=  dbContext.BoxGrids;

                foreach (DataRow row in dataTable.Rows)
                {
                    bool res = materials.Any(x => x.MaterialCode == row["MaterialCode"].ToString());
                    if (res)
                    {
                        continue;
                    }
                    MaterialBase materialBase = new MaterialBase();
                    materialBase.MaterialCode = row["MaterialCode"].ToString();
                    materialBase.Quantity = (int?)row["Quantity"];
                    materialBase.MaterialName= row["MaterialName"].ToString();
                    boxgrids.FirstOrDefaultAsync(x => x.BoxGridCode == row["BoxGridCode"].ToString());

                    materials.Add(materialBase);
                }
                dbContext.SaveChanges();
            }

        }
        //导入盘点数据
        public static void ExcelImportStockTaking()
        {

            try
            {
                string excelName = OpenfileDialog();
                var dataTable = ExcelImportMethod(excelName);
                if (dataTable == null)
                {
                    return;
                }
                if(dataTable.Columns.Count != 1)
                {
                    MessageBox.Show("请检查数据格式");
                    return;
                }
                if (dataTable.Rows.Count > 100)
                {
                    MessageBox.Show("每次最多导入100行");
                    return;
                }
                using (MyDbContext dbContext = new MyDbContext())
                {
                    List<CustomCheckList> customCheckLists = new List<CustomCheckList>();
                    var materials = dbContext.CustomCheckList;

                    foreach (DataRow row in dataTable.Rows)
                    {
                        CustomCheckList customCheckList = new CustomCheckList();
                        customCheckList.CustomCheckList_Id = Guid.NewGuid();
                        customCheckList.Consumer_MaterialId = row["Consumer_MaterialId"].ToString();
                        customCheckList.CreateTime = DateTime.Now;
                        customCheckLists.Add(customCheckList);
                    }
                    dbContext.AddRange(customCheckLists);
                    dbContext.SaveChanges();
                    MessageBox.Show("导入成功");
                }
            }catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        
        }




        //
        public static string OpenfileDialog()
        {
            // 创建 OpenFileDialog 实例
            OpenFileDialog openFileDialog = new OpenFileDialog();

            // 设置对话框标题
            openFileDialog.Title = "请选择一个文件";

            // 设置默认目录（可选）
            openFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

            // 设置文件过滤条件（可选）
            openFileDialog.Filter = "Excel 97-2003工作簿 (*.xls)|*.xls|Excel 工作簿 (*.xlsx)|*.xlsx|所有文件 (*.*)|*.*";

            // 显示对话框并等待用户操作
            DialogResult result = openFileDialog.ShowDialog();

            // 如果用户点击了“确定”按钮
            if (result == DialogResult.OK)
            {
                // 获取并打印用户选择的文件路径
                string filePath = openFileDialog.FileName;
                return filePath;
            }
            else
            {
                // 用户取消了选择
                return null;
            }
        }
        public static DataTable ExcelImportMethod(string excelFileName)
        {
            DataTable dt = new DataTable();
            ISheet sheet = null;
            try
            {
                using (FileStream file = new FileStream(excelFileName, FileMode.Open, FileAccess.Read))
                {
                    if (excelFileName.IndexOf(".xlsx") <= 0)
                    {
                        HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                        sheet = hssfworkbook.GetSheetAt(0);
                    }
                    else
                    {
                        XSSFWorkbook xssfworkbook = new XSSFWorkbook(file);
                        sheet = xssfworkbook.GetSheetAt(0);
                    }
                }
            }
            catch
            {
                MessageBox.Show("请检查文件或关闭文件");
                return null;
            }
  

            IEnumerator rows = sheet.GetRowEnumerator();
            IRow headerrow = sheet.GetRow(0);
            int cellCount = headerrow.LastCellNum;
            for (int j = 0; j < cellCount; j++)
            {
                ICell cell = headerrow.GetCell(j);
                if (cell == null)
                {
                    continue;
                }
                dt.Columns.Add(cell.ToString());
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                DataRow dataRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                  if(row.GetCell(j) != null)
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                 
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }

    }
}
